﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Eneta.Portal.Common.Dto;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Publishing;

namespace Eneta.Portal.Common.Repository
{
    /// <summary>
    /// Class for companies operations. 
    /// </summary>
    public class CompanyRepository :  BaseRepository
    {
        public CompanyRepository(SPWeb web) : base(web)
        {
        }

        /// <summary>
        /// Gets the random company. 
        /// </summary>
        public CompanyDto GetRandomCompany()
        {
            CompanyDto dto = null;

            var sec = new SPSecurity.CodeToRunElevated(delegate
                       {
                           using(var site = new SPSite(SiteId))
                           using(var web = site.OpenWeb("/firmad/"))
                           {
                               dto = GetRandomCompany(web);
                           }
                       });
            SPSecurity.RunWithElevatedPrivileges(sec);

            return dto;
        }

        private CompanyDto GetRandomCompany(SPWeb web)
        {
            var list = web.Lists["Lehed"];

            var queryString = "<Where><And><Eq><FieldRef Name='ContentType' /><Value Type='Text'>Firma</Value></Eq>";
            queryString += "<Eq><FieldRef Name='_ModerationStatus' /><Value Type='ModStat'>{0}</Value></Eq></And></Where>";
            queryString = string.Format(queryString, Resources.ModerationStatusApproved);

            var items = ExecuteQuery(list, queryString);
            if (items == null)
                return null;
            if (items.Count == 0)
                return null;

            var random = new Random(DateTime.Now.Millisecond);
            var index = random.Next(0, items.Count - 1);
            var item = items[index];

            return CreateCompany(item);
        }

        /// <summary>
        /// Gets the list of user companies.
        /// </summary>
        /// <param name="userId">The user id.</param>
        /// <param name="onlyApproved">if set to <c>true</c> returns only approved companies.</param>
        public IList<CompanyDto> GetUserCompanies(int userId, bool onlyApproved)
        {
            IList<CompanyDto> companies = new List<CompanyDto>();

            var sec = new SPSecurity.CodeToRunElevated(delegate
                {
                    using (var site = new SPSite(SiteId))
                    using (var web = site.OpenWeb("/firmad/"))
                    {
                        companies = GetUserCompanies(web, userId, onlyApproved);
                    }
                });
            SPSecurity.RunWithElevatedPrivileges(sec);

            return companies;
        }

        /// <summary>
        /// Gets the list of user companies.
        /// </summary>
        /// <param name="userName">User name.</param>
        /// <param name="onlyApproved">if set to <c>true</c> returns only approved companies.</param>
        /// <returns></returns>
        public IList<CompanyDto> GetUserCompanies(string userName, bool onlyApproved)
        {
            IList<CompanyDto> companies = new List<CompanyDto>();

            var sec = new SPSecurity.CodeToRunElevated(delegate
            {
                using (var site = new SPSite(SiteId))
                using (var web = site.OpenWeb("/firmad/"))
                {
                    var user = web.SiteUsers[userName];
                    companies = GetUserCompanies(web, user.ID, onlyApproved);
                }
            });
            SPSecurity.RunWithElevatedPrivileges(sec);

            return companies;
        }

        private IList<CompanyDto> GetUserCompanies(SPWeb web, int userId, bool onlyApproved)
        {
            var queryString = "<Where><And><Eq><FieldRef Name='RealAuthor' LookupId='True' /><Value Type='User'>{0}</Value></Eq>";
            queryString += "<Eq><FieldRef Name='ContentType' /><Value Type='Text'>Firma</Value></Eq>";
            queryString += "</And></Where>";
            queryString = string.Format(queryString, userId);

            var list = web.Lists["Lehed"];
            var items = ExecuteQuery(list, queryString);
            var result = CreateCompanies(items);

            if (result != null)
                if (onlyApproved)
                {
                    result = (from c in result
                              where c.Status == SPModerationStatusType.Approved
                              select c).ToList();
                }
            return result;
        }

        /// <summary>
        /// Gets the list of pending companies.
        /// </summary>
        public IList<CompanyDto> GetPendingCompanies()
        {
            IList<CompanyDto> result = new List<CompanyDto>();

            var sec = new SPSecurity.CodeToRunElevated(delegate
                  {
                      using(var site = new SPSite(SiteId))
                      using(var web = site.OpenWeb("/firmad/"))
                      {
                          result = GetPendingCompanies(web);
                      }
                  }                                         
                );
            SPSecurity.RunWithElevatedPrivileges(sec);

            return result;
        }

        private IList<CompanyDto> GetPendingCompanies(SPWeb web)
        {
            IList<CompanyDto> results = new List<CompanyDto>();
            var list = web.Lists["Lehed"];

            var queryString = @"<Where>
                       <Eq>
                         <FieldRef Name='_ModerationStatus'/>
                         <Value Type='ModStat'>{0}</Value>
                       </Eq>
                    </Where>";
            queryString = string.Format(queryString, Resources.ModerationStatusPending);

            var items = ExecuteQuery(list, queryString);
            if (items == null)
                return results;

            if (items.Count == 0)
                return results;

            results = CreateCompanies(items);
            return results;
        }

        /// <summary>
        /// Gets the list of approved companies.
        /// </summary>
        public IList<CompanyDto> GetApprovedCompanies()
        {
            if (HttpContext.Current != null)
                if (HttpContext.Current.Items["ApprovedCompanies"] != null)
                    return (IList<CompanyDto>) HttpContext.Current.Items["ApprovedCompanies"];

            IList<CompanyDto> results = new List<CompanyDto>();

            var sec = new SPSecurity.CodeToRunElevated(delegate
            {
                using (var site = new SPSite(SiteId))
                using (var web = site.OpenWeb("/firmad/"))
                {
                    var list = web.Lists["Lehed"];
                    var items = GetApprovedItems(list, "Firma", string.Empty, false);
                    results = CreateCompanies(items);
                }
            });
            SPSecurity.RunWithElevatedPrivileges(sec);

            if (HttpContext.Current != null)
                HttpContext.Current.Items["ApprovedCompanies"] = results;
            return results;
        }

        /// <summary>
        /// Gets the list of unapproved companies.
        /// </summary>
        public IList<CompanyDto> GetUnapprovedCompanies()
        {
            IList<CompanyDto> results = new List<CompanyDto>();
            
            var sec = new SPSecurity.CodeToRunElevated(delegate
                       {
                           using(var site = new SPSite(SiteId))
                           using(var web = site.OpenWeb("/firmad/"))
                           {
                               var list = web.Lists["Lehed"];

                               var queryString = "<Where><Neq><FieldRef Name='_ModerationStatus'/><Value Type='ModStat'>{0}</Value></Neq></Where>";
                               queryString = string.Format(queryString, Resources.ModerationStatusApproved);

                               var items = ExecuteQuery(list, queryString);
                               results = CreateCompanies(items);                               
                           }
                       });
            SPSecurity.RunWithElevatedPrivileges(sec);
            return results;
        }

        /// <summary>
        /// Checks if companies with given name exists.
        /// </summary>
        /// <param name="name">The name to check.</param>
        public bool CompanyExists(string name)
        {
            var exists = false;

            var sec = new SPSecurity.CodeToRunElevated(delegate
                        {
                            using(var site = new SPSite(SiteId))
                            using(var web = site.OpenWeb("/firmad/"))
                            {
                                exists = CompanyExists(web, name); 
                            }
                        });
            SPSecurity.RunWithElevatedPrivileges(sec);

            return exists;
        }

        private bool CompanyExists(SPWeb web, string name)
        {
            var list = web.Lists["Lehed"];
            var queryString = "<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>{0}</Value></Eq></Where>";
            queryString = string.Format(queryString, name);

            var items = ExecuteQuery(list, queryString);
            if (items == null)
                return false;

            return (items.Count > 0);
        }

        /// <summary>
        /// Checks if companies page layout exists.
        /// </summary>
        public bool CompanyLayoutExists()
        {
            var result = false;

            var sec = new SPSecurity.CodeToRunElevated(delegate
                       {
                           using(var site = new SPSite(SiteId))
                           using(var web = site.OpenWeb("/firmad/"))
                           {
                               var publishingWeb = PublishingWeb.GetPublishingWeb(web);

                               foreach (var layout2 in publishingWeb.GetAvailablePageLayouts())
                               {
                                   if (layout2.Name.ToLower() == "eneta.company.detailsview.layout.aspx")
                                   {
                                       result = true;
                                       break;
                                   }
                               }
                           }
                       });
            SPSecurity.RunWithElevatedPrivileges(sec);

            return result;
        }

        /// <summary>
        /// Gets the company by id.
        /// </summary>
        /// <param name="companyId">The company id.</param>
        public CompanyDto GetCompanyById(int companyId)
        {
            CompanyDto dto = null;
            var sec = new SPSecurity.CodeToRunElevated(delegate
                              {
                                  using(var site = new SPSite(SiteId))
                                  using(var web = site.OpenWeb("/firmad/"))
                                  {
                                      dto = GetCompanyById(web, companyId);
                                  }
                              }
                            );
            SPSecurity.RunWithElevatedPrivileges(sec);
            return dto;
        }

        /// <summary>
        /// Gets the company by id.
        /// </summary>
        /// <param name="web">The web of companies list.</param>
        /// <param name="companyId">The company id.</param>
        public CompanyDto GetCompanyById(SPWeb web, int companyId)
        {
            var list = web.Lists["Lehed"];
            var companyItem = QueryItemByFieldValue(list, "ID", companyId.ToString());
            return CreateCompany(companyItem);
        }

        /// <summary>
        /// Gets the company page layout.
        /// </summary>
        public PageLayout GetCompanyLayout()
        {
            PageLayout result = null;

            var sec = new SPSecurity.CodeToRunElevated(delegate
                        {
                            using (var site = new SPSite(SiteId))
                            using (var web = site.OpenWeb("/firmad/"))
                            {
                                var publishingWeb = PublishingWeb.GetPublishingWeb(web);

                                foreach (var layout in publishingWeb.GetAvailablePageLayouts())
                                {
                                    if (layout.Name.ToLower() == "eneta.company.detailsview.layout.aspx")
                                    {
                                        result = layout;
                                        break;
                                    }
                                }
                            }
                        });
            SPSecurity.RunWithElevatedPrivileges(sec);

            return result;            
        }

        /// <summary>
        /// Gets the list of IO levels.
        /// </summary>
        public IList<IoLevel1> GetIoLevels()
        {
            IList<IoLevel1> results = new List<IoLevel1>(); //GetFromCache<List<IoLevel1>>("IoLevels");
            //if (results != null)
            //    return results;

            var sec = new SPSecurity.CodeToRunElevated(delegate
                {
                    using (var site = new SPSite(SiteId))
                    using (var web = site.OpenWeb("/firmad/"))
                    {
                        results = GetIoLevels(web);
                    }
                });
            SPSecurity.RunWithElevatedPrivileges(sec);

            SaveToCache("IoResults", results);
            return results;
        }

        private IList<IoLevel1> GetIoLevels(SPWeb web)
        {
            var list = web.Lists["IOLevel1"];
            var results = new List<IoLevel1>();

            var items = list.Items;
            if(items == null)
                return results;
            if (items.Count == 0)
                return results;

            foreach (SPListItem item in items)
                results.Add(CreateIoLevel1(item));

            return results;
        }

        private IoLevel1 CreateIoLevel1(SPListItem item)
        {
            if (item == null)
                return null;

            var level = new IoLevel1
                            {
                                Id = item.ID, 
                                Title = item.Title
                            };

            var queryString = "<Where><Eq><FieldRef Name='Level1' LookupId='True' /><Value Type='Lookup'>{0}</Value></Eq></Where>";
            queryString = string.Format(queryString, level.Id);

            var items = ExecuteQuery(item.ParentList.ParentWeb.Lists["IOLevel2"], queryString);
            foreach (SPListItem level2Item in items)
                level.Level2Items.Add(CreateIoLevel2(level2Item, level));

            return level;
        }

        private IoLevel2 CreateIoLevel2(SPListItem item, IoLevel1 parentLevel)
        {
            if (item == null || parentLevel == null)
                return null;

            var level = new IoLevel2
                            {
                                Id = item.ID, 
                                Title = item.Title, 
                                Level1 = parentLevel
                            };

            var queryString = "<Where><Eq><FieldRef Name='Level2' LookupId='True' /><Value Type='Lookup'>{0}</Value></Eq></Where>";
            queryString = string.Format(queryString, level.Id);

            var items = ExecuteQuery(item.ParentList.ParentWeb.Lists["IOTopic"], queryString);
            foreach (SPListItem level3Item in items)
                level.Level3Items.Add(CreateIoLevel3(level3Item, level));

            return level;
        }

        private static IoLevel3 CreateIoLevel3(SPListItem item, IoLevel2 parentLevel)
        {
            if (item == null || parentLevel == null)
                return null;

            var level = new IoLevel3
                            {
                                Id = item.ID, 
                                Title = item.Title, 
                                Level2 = parentLevel,
                                Products = new List<IoProduct>()
                            };

            if(item["IOProducts"] != null)
            {
                var products = new SPFieldLookupValueCollection(item["IOProducts"].ToString());
                var productList = item.ParentList.ParentWeb.Lists["IOProducts"];

                foreach(var product in products)
                {
                    var productItem = productList.GetItemById(product.LookupId);
                    level.Products.Add(CreateProduct(productItem));
                }
            }

            return level;
        }

        private static IoProduct CreateProduct(SPListItem productItem)
        {
            if (productItem == null)
                return null;

            var dto = new IoProduct
                          {
                              Id = productItem.ID, 
                              Title = productItem.Title
                          };

            if(productItem["ProductLink"] != null)
            {
                var link = new SPFieldUrlValue(productItem["ProductLink"].ToString());
                dto.Url = link.Url;
            }

            return dto;
        }
            
        private static IList<CompanyDto> CreateCompanies(SPListItemCollection companyItems)
        {
            IList<CompanyDto> result = new List<CompanyDto>();

            if (companyItems == null)
                return result;

            foreach (SPListItem item in companyItems)
                result.Add(CreateCompany(item));

            return result;
        }

        private static CompanyDto CreateCompany(SPListItem companyItem)
        {
            if (companyItem == null)
                return null;

            var dto = new CompanyDto
                          {
                              Id = companyItem.ID, 
                              Title = companyItem.Title,
                              Created = (DateTime)companyItem["Created"],
                              Modified = (DateTime)companyItem["Modified"]
                          };

            var fieldId = companyItem.Fields.GetFieldByInternalName("Tanav").Id;
            if (companyItem[fieldId] != null)
                dto.Street = companyItem[fieldId].ToString();

            fieldId = companyItem.Fields.GetFieldByInternalName("Postiindeks").Id;
            if (companyItem[fieldId] != null)
                dto.Zip = companyItem[fieldId].ToString();

            fieldId = companyItem.Fields.GetFieldByInternalName("WorkCity").Id;
            if (companyItem[fieldId] != null)
                dto.City = companyItem[fieldId].ToString();

            fieldId = companyItem.Fields.GetFieldByInternalName("WebPage").Id;
            if (companyItem[fieldId] != null)
            {
                var urlValue = new SPFieldUrlValue(companyItem[fieldId].ToString());
                dto.HomePage = urlValue.Url;
            }

            fieldId = companyItem.Fields.GetFieldByInternalName("Employees").Id;
            if (companyItem[fieldId] != null)
                dto.Employees = companyItem[fieldId].ToString();

            fieldId = companyItem.Fields.GetFieldByInternalName("CertifiedEmployees").Id;
            if (companyItem[fieldId] != null)
                dto.CertifiedEmployees = companyItem[fieldId].ToString();

            fieldId = companyItem.Fields.GetFieldByInternalName("PartnershipLevel").Id;
            if (companyItem[fieldId] != null)
                dto.PartnershipLevel = companyItem[fieldId].ToString();

            fieldId = companyItem.Fields.GetFieldByInternalName("PublishingPageContent").Id;
            if (companyItem[fieldId] != null)
                dto.Description = companyItem[fieldId].ToString();

            fieldId = companyItem.Fields.GetFieldByInternalName("Comments").Id;
            if (companyItem[fieldId] != null)
                dto.ShortDescription = companyItem[fieldId].ToString();

            fieldId = companyItem.Fields.GetFieldByInternalName("Kontaktisik").Id;
            if (companyItem[fieldId] != null)
                dto.ContactName = companyItem[fieldId].ToString();

            fieldId = companyItem.Fields.GetFieldByInternalName("KontaktisikuEmail").Id;
            if (companyItem[fieldId] != null)
                dto.ContactEmail = companyItem[fieldId].ToString();

            fieldId = companyItem.Fields.GetFieldByInternalName("KontaktisikuTelefon").Id;
            if (companyItem[fieldId] != null)
                dto.ContactPhone = companyItem[fieldId].ToString();

            if(string.IsNullOrEmpty(dto.Description))
                if(!string.IsNullOrEmpty(dto.ShortDescription))
                {
                    dto.Description = dto.ShortDescription;
                }

            if (!string.IsNullOrEmpty(dto.Description))
                if (dto.Description.Length > 255)
                    dto.ShortDescription = dto.Description.Substring(0, 255) + "...";
                else
                    dto.ShortDescription = dto.Description;

            fieldId = companyItem.Fields.GetFieldByInternalName("FileLeafRef").Id;
            if (companyItem[fieldId] != null)
                dto.FileName = companyItem[fieldId].ToString();

            fieldId = companyItem.Fields.GetFieldByInternalName("Kontorid").Id;
            if(companyItem[fieldId] != null)
            {
                var officeValues = new SPFieldLookupValueCollection(companyItem[fieldId].ToString());
                foreach (var office in officeValues)
                {
                    var officeDto = new OfficeDto
                                        {
                                            Id = office.LookupId,
                                            Title = office.LookupValue 
                                        };
                    dto.Offices.Add(officeDto);
                }
            }

            if (companyItem["RealAuthor"] != null)
            {
                var userValue = new SPFieldUserValue(companyItem.ParentList.ParentWeb, companyItem["RealAuthor"].ToString());
                var user = userValue.User;

                if (user != null)
                {
                    dto.AuthorUserName = user.LoginName;

                    var userRep = new UserProfileRepository(companyItem.ParentList.ParentWeb);
                    var profile = userRep.GetUserProfileByUserName(user.LoginName);
                    if (profile != null)
                    {
                        dto.OwnerProfileId = profile.Id;
                        dto.OwnerProfileName = profile.UserName;
                    }
                }
            }

            if(companyItem.ModerationInformation != null)
            {
                dto.Status = companyItem.ModerationInformation.Status;
                dto.StatusComment = companyItem.ModerationInformation.Comment;
            }

            return dto;
        }

        /// <summary>
        /// Gets the companies filtered result set. 
        /// </summary>
        /// <returns></returns>
        public IList<CompanyDto> GetCompanyResults()
        {
            if (HttpContext.Current != null)
                if (HttpContext.Current.Items["CompanyViewResults"] != null)
                    return (IList<CompanyDto>) HttpContext.Current.Items["CompanyViewResults"];

            IList<CompanyDto> results = new List<CompanyDto>();

            var sec = new SPSecurity.CodeToRunElevated(delegate
                            {
                                using(var site = new SPSite(SiteId))
                                using(var web = site.OpenWeb("/firmad/"))
                                {
                                    results = GetCompanyResults(web);
                                }
                            }                                     
                        );
            SPSecurity.RunWithElevatedPrivileges(sec);

            if (HttpContext.Current != null)
                HttpContext.Current.Items["CompanyViewResults"] = results;
            return results;
        }

        private IList<CompanyDto> GetCompanyResults(SPWeb web)
        {
            var levels = GetVisibleIoLevels();
            if (levels == null)
                return null;

            var companies = GetApprovedCompanies();
            if (companies == null)
                return null;

            var relations = GetIoRelations(web);
            if (relations == null)
                return null;

            var level1 = 0;
            var level3 = 0;
            var product = 0;

            var county = string.Empty;
            var employees = string.Empty;
            var partnership = string.Empty;

            if(HttpContext.Current != null)
            {
                var request = HttpContext.Current.Request;
                int.TryParse(request.QueryString["l1"], out level1);
                int.TryParse(request.QueryString["l3"], out level3);
                int.TryParse(request.QueryString["p"], out product);

                county = request.QueryString["county"] ?? string.Empty;
                employees = request.QueryString["emp"] ?? string.Empty;
                partnership = request.QueryString["partnership"] ?? string.Empty;
            }
                                            
            var query = from l1 in levels
                        from l2 in l1.Level2Items
                        from l3 in l2.Level3Items
                        from r in relations
                        from c in companies
                        where
                            (l1.Id == level1 || level1 == 0)
                            && (l3.Id == level3 || level3 == 0)
                            && (l3.Products.Count(p => p.Id == product) > 0 || product == 0)
                            && (r.Level3Id == l3.Id)
                            && (r.CompanyId == c.Id)
                            && (c.Employees == employees || string.IsNullOrEmpty(employees))
                            && (c.PartnershipLevel.ToLower() == partnership.ToLower() || string.IsNullOrEmpty(partnership))
                            && (c.Offices.Count(o => o.Title.ToLower() == county.ToLower()) > 0 || string.IsNullOrEmpty(county))
                        select c;
            if (query.Count() == 0)
                return null;

            var result = query.Distinct().ToList();
            return result;
        }

        /// <summary>
        /// Gets the active IO first level.
        /// </summary>
        public IoLevel1 GetActiveLevel1()
        {
            var l1 = HttpContext.Current.Request.QueryString["l1"] ?? string.Empty;
            var l3 = HttpContext.Current.Request.QueryString["l3"] ?? string.Empty;
            var levels = GetVisibleIoLevels();

            int level1, level3;

            int.TryParse(l1, out level1);
            int.TryParse(l3, out level3);

            IEnumerable<IoLevel1> query;

            if (level1 != 0)
                query = from l in levels
                        where l.Id == level1
                        select l;
            else if (level3 != 0)
                query = from lv1 in GetVisibleIoLevels()
                        from lv2 in lv1.Level2Items
                        from lv3 in lv2.Level3Items
                        where lv3.Id == level3
                        select lv1;
            else
                return null;

            if (query.Count() == 0)
                return null;

            return query.First();
        }

        /// <summary>
        /// Gets the active IO third level.
        /// </summary>
        public IoLevel3 GetActiveLevel3()
        {
            var l3 = HttpContext.Current.Request.QueryString["l3"] ?? string.Empty;
            if (string.IsNullOrEmpty(l3))
                return null;

            int level3;
            if (!int.TryParse(l3, out level3))
                return null;
                
            var levels = GetVisibleIoLevels();
            var query = from lv1 in levels
                        from lv2 in lv1.Level2Items 
                        from lv3 in lv2.Level3Items 
                        where lv3.Id == level3
                        select lv3;

            if (query.Count() == 0)
                return null;

            return query.First();
        }

        public IoProduct GetActiveProduct()
        {
            var p = HttpContext.Current.Request.QueryString["p"] ?? string.Empty;
            if (string.IsNullOrEmpty(p))
                return null;

            int product;
            if (!int.TryParse(p, out product))
                return null;

            var levels = GetVisibleIoLevels();
            var query = from lv1 in levels
                        from lv2 in lv1.Level2Items
                        from lv3 in lv2.Level3Items
                        from pr in lv3.Products 
                        where pr.Id == product 
                        select pr;
            
            if (query.Count() == 0)
                return null;

            return query.First();
        }

        public IList<IoLevel1> GetLevel1ForProduct(IoProduct product)
        {
            var levels = GetVisibleIoLevels();
            var query = from lv1 in levels
                        from lv2 in lv1.Level2Items
                        from lv3 in lv2.Level3Items
                        from pr in lv3.Products
                        where pr.Id == product.Id
                        select lv1;

            if (query.Count() == 0)
                return null;

            return query.ToList();
        }

        public IList<IoLevel2> GetLevel2ForProduct(IoProduct product)
        {
            var levels = GetVisibleIoLevels();
            var query = from lv1 in levels
                        from lv2 in lv1.Level2Items
                        from lv3 in lv2.Level3Items
                        from pr in lv3.Products
                        where pr.Id == product.Id
                        select lv2;

            if (query.Count() == 0)
                return null;

            return query.ToList();
        }

        public IList<IoLevel3> GetLevel3ForProduct(IoProduct product)
        {
            var levels = GetVisibleIoLevels();
            var query = from lv1 in levels
                        from lv2 in lv1.Level2Items
                        from lv3 in lv2.Level3Items
                        from pr in lv3.Products
                        where pr.Id == product.Id
                        select lv3;

            if (query.Count() == 0)
                return null;

            return query.ToList();
        }

        /// <summary>
        /// Gets the list of available counties in the context of current results.
        /// </summary>
        public IList<OfficeDto> GetCountyResults()
        {
            var companies = GetCompanyResults();
            if (companies == null)
                return new List<OfficeDto>();

            var query = from c in companies
                        from o in c.Offices
                        where c.Offices != null
                        select o;
            if (query.Count() == 0)
                return new List<OfficeDto>();

            return query.Distinct().ToList();
        }

        public IList<string> GetEmployees()
        {
            var results = new List<string>();

            SPSecurity.RunWithElevatedPrivileges(new SPSecurity.CodeToRunElevated(delegate
            {

                using (var site = new SPSite(SiteId))
                using (var web = site.OpenWeb())
                {
                    var col = (SPFieldChoice)web.Fields["Töötajaid"];

                    foreach (var choice in col.Choices)
                        results.Add(choice);
                }
            }));

            return results;        
        }

        /// <summary>
        /// Gets the list of employee counts in the context of current results.
        /// </summary>
        /// <returns></returns>
        public IList<string> GetEmployeeResults()
        {
            var companies = GetCompanyResults();
            if (companies == null)
                return new List<string>();

            var query = from c in companies
                        select c.Employees;

            return query.Distinct().ToList();
        }

        public IList<string> GetPartnerships()
        {
            var results = new List<string>();

            SPSecurity.RunWithElevatedPrivileges(new SPSecurity.CodeToRunElevated(delegate
            {

                using (var site = new SPSite(SiteId))
                using (var web = site.OpenWeb())
                {
                    var col = (SPFieldChoice)web.Fields["PartnershipLevel"];

                    foreach (var choice in col.Choices)
                        results.Add(choice);
                }
            }));

            return results;
        }

        /// <summary>
        /// Gets the list of partnership levels in the context of current results.
        /// </summary>
        public IList<string> GetPartnershipResults()
        {
            var companies = GetCompanyResults();
            if (companies == null)
                return new List<string>();

            var query = from c in companies
                        select c.PartnershipLevel;

            return query.Distinct().ToList();
        }

        /// <summary>
        /// Gets the list of products in the context of current results.
        /// </summary>
        public IList<IoProduct> GetProductResults()
        {
            var level1 = GetActiveLevel1();
            var level1Id = 0;
            if (level1 != null)
                level1Id = level1.Id;

            var level3 = GetActiveLevel3();
            var level3Id = 0;
            if (level3 != null)
                level3Id = level3.Id;

            if (level1Id == 0 && level3Id == 0)
                return GetAllProductsResults();

            var levels = GetVisibleIoLevels();

            var query = from l1 in levels
                        from l2 in l1.Level2Items
                        from l3 in l2.Level3Items
                        from p in l3.Products
                        where 
                            (l1.Id == level1Id || level1Id == 0) &&
                            (l3.Id == level3Id || level3Id == 0)
                        select p;

            return query.Distinct().ToList();
        }

        /// <summary>
        /// Gets the list of all available products.
        /// </summary>
        public IList<IoProduct> GetAllProductsResults()
        {
            var levels = GetVisibleIoLevels();

            if (levels == null)
                return null;

            var query = from level1 in levels
                        from level2 in level1.Level2Items
                        from level3 in level2.Level3Items
                        from product in level3.Products
                        select product;

            return query.Distinct().ToList();
        }

        /// <summary>
        /// Gets the list of visible IO levels.
        /// </summary>
        public IList<IoLevel1> GetVisibleIoLevels()
        {
            IList<IoLevel1> levels = GetFromCache<List<IoLevel1>>("VisibleIoLevels");
            if (levels != null)
                return levels;

            var sec = new SPSecurity.CodeToRunElevated(delegate
                  {
                      using(var site = new SPSite(SiteId))
                      using(var web = site.OpenWeb("/firmad/"))
                      {
                          levels = GetVisibleIoLevels(web);
                      } 
                  }                                         
                );
            SPSecurity.RunWithElevatedPrivileges(sec);

            SaveToCache("VisibleIoLevels", levels);
            return levels;
        }

        private IList<IoLevel1> GetVisibleIoLevels(SPWeb web)
        {
            var levels = GetFromCache<IList<IoLevel1>>("VisibleIoLevels");
            if (levels != null)
                return levels;
                
            levels = GetIoLevels();
            var activeRelations = GetActiveIoRelations(web);
            var query = from a in activeRelations
                        select a.Level3Id;
            var leve3Ids = query.ToArray();

            if(levels.Count == 0)
            {
                Logger.LogInfo("GetVisibleIOLevels(): IO Levels count is zero.", GetType().ToString());
                return null;
            }

            for(var i1 = levels.Count-1; i1 >= 0; i1--)
            {
                var i1Item = levels[i1];
                for (var i2 = i1Item.Level2Items.Count - 1; i2 >= 0; i2--)
                {
                    var i2Item = i1Item.Level2Items[i2];
                    for(var i3 = i2Item.Level3Items.Count-1; i3 >= 0; i3--)
                    {
                        var i3Item = i2Item.Level3Items[i3];
                        if (!leve3Ids.Contains(i3Item.Id))
                            i2Item.Level3Items.RemoveAt(i3);
                    }
                }
                if (i1Item.Level2Items.Count == 0)
                    levels.RemoveAt(i1);
            }

            SaveToCache("VisibleIoLevels", levels);
            return levels;
        }

        private static IList<IoRelation> GetIoRelations(SPWeb web)
        {
            var list = web.Lists["IOCompany"];
            var results = new List<IoRelation>();

            foreach(SPListItem item in list.Items)
            {
                var companyValue = new SPFieldLookupValue(item["Company"].ToString());
                var level3Values = new SPFieldLookupValueCollection(item["IOTopic"].ToString());

                foreach (var level3 in level3Values)
                {
                    var dto = new IoRelation
                                  {
                                      CompanyId = companyValue.LookupId,
                                      Level3Id = level3.LookupId
                                  };
                    results.Add(dto);
                }
            }

            return results;
        }

        private IList<IoRelation> GetActiveIoRelations(SPWeb web)
        {
            var results = GetFromCache<IList<IoRelation>>("IoRelations");
            if (results != null)
                return results;

            var relations = GetIoRelations(web);
            var companies = GetApprovedCompanies();

            var active = from rel in relations
                         from com in companies
                         where rel.CompanyId == com.Id
                         select rel;
            results = active.ToList();

            SaveToCache("IoRelations", results);
            return results;
        }

        
    }
}
